<?php
class Order extends ActiveRecord {
	
	protected $tableName = 'order';
	public $pkName = 'ID_order';
	
	protected $arrSettings = array(
			'created_at' => true,  // в БД храним время создания
			'updated_at' => true, 	// в БД храним время редактирования
	);
	public $validatesLengthOf = array(
			'description' => array('minimum' => 2, 'maximum' => 1024, 'message' => 'Укажите описание груза'),
			'one_phone' => array('minimum' => 4, 'maximum' => 16, 'message' => 'Укажите хотя бы один номер телефона(от 4 до 15 цифр)')
	);
	
	public $validatesFormatOf = array(
			'one_phone' => array('message' => 'Поле для телефона может содержать только цифры', 'with' => '|^[0-9]+$|','allowNull' => true),
			'another_phone' => array('message' => 'Поле для телефона может содержать только цифры', 'with' => '|^[0-9]+$|','allowNull' => true),
			'weight' => array('message' => 'Вес груза - число в тоннах, но не более 9999', 'with' => '|^[0-9]{1,4}$|','allowNull' => true),
			'volume' => array('message' => 'ОбЪём груза - число в метрах кубических, но не более 9999', 'with' => '|^[0-9]{1,4}$|','allowNull' => true),
			'clients_price' => array('message' => 'Цена - число до 999999', 'with' => '|^[0-9]{1,6}$|','allowNull' => true),
			'our_price' => array('message' => 'Цена - число до 999999', 'with' => '|^[0-9]{1,6}$|','allowNull' => true),
			'prepayment' => array('message' => 'Сумма или процент предоплаты - число', 'with' => '|^[0-9]{1,9}$|','allowNull' => true),
			'delivery_date_plan' => array('message' => 'Неверный формат предварительной даты', 'with' => '#^\d{2,4}([-])\d{1,2}([-])\d{1,2}$#','allowNull' => true),
			'delivery_date_fact' => array('message' => 'Неверный формат итоговой даты', 'with' => '#^\d{2,4}([-])\d{1,2}([-])\d{1,2}$#','allowNull' => true),
	);
	
	public function beforeValidation() {
		Service_Debug::setDebug();
		// НАДО селать проверку связности при добавлении/редактировании заказа : получен существующий водитель и существующие города
		$objCity = new City();
		if($objCity->getIdByName($this->getField('city_from_string'))) $this->setField('city_from', $objCity->getIdByName($this->getField('city_from_string')));
		if($objCity->getIdByName($this->getField('city_to_string')))  $this->setField('city_to', $objCity->getIdByName($this->getField('city_to_string')));
		if(isset($this->tableFields['city_from_string'])) unset($this->tableFields['city_from_string']);
		if(isset($this->tableFields['city_to_string'])) unset($this->tableFields['city_to_string']);
		
		// Если отмечено не "частичная предоплата" затираем строку с суммой
		if(isset($this->tableFields['prepayment_type']) && $this->tableFields['prepayment_type'] != '2') $this->tableFields['prepayment'] = null;
		// Если строка с суммой пуста  и отмечено "частичная предоплата", то отмечает "нет данных"
		if(isset($this->tableFields['prepayment']) && $this->tableFields['prepayment'] == '' && isset($this->tableFields['prepayment_type']) && $this->tableFields['prepayment_type'] == '2') $this->tableFields['prepayment_type'] = null;
		
		// Если строка водителя пуста, затираем ID
		if(isset($this->tableFields['driver_string'])) {
			if($this->tableFields['driver_string'] == '') $this->tableFields['ID_driver'] = null;
			unset($this->tableFields['driver_string']);
		}
	} 
	
	public function beforeValidationOnCreate () {
		Service_Debug::setDebug();
		$objSession = User_Session::instance();
		if(!$objSession->isLoggedIn() && isset($this->tableFields['first_name'])) {
				$this->validatesLengthOf['first_name'] = array ('minimum' => 2, 'maximum' => 64, 'message' => 'Слишком короткое имя');
				$this->validatesLengthOf['last_name'] = array ('minimum' => 2, 'maximum' => 64, 'message' =>'Слишком короткая фамилия');
				$this->validatesLengthOf['pass'] = array('minimum' => 6, 'maximum' => 32, 'message' => 'Пароль введен неверно, должен состоять из 6-128 символов', 'on' => 'create');
				$this->validatesFormatOf['e_mail'] =  array('message' => 'Электронная почта заполнена неверно', 'with' => '|^[\.\-_A-Za-z0-9]+?@[\.\-A-Za-z0-9]+?\.[A-Za-z0-9]{2,6}$|','on' => 'create');
				$this->validatesConfirmationOf['pass'] = array('message' => 'Пароль и его подтверждение не совпадают');
					
				// Проверить ещё и почту на существование!!!
			
			
			//if (!isset($this->tableFields['kaptcha']) || empty($this->tableFields['kaptcha']) || ($objSession->__get('keystring')!=$this->tableFields['kaptcha'])) {
				//$error = Service_Translate::getText('Неверный код подтверждения');
				//Exception_ActiveRecord::addObjectException(get_class($this), 'kaptcha', $error);
			//}
		}
	}
	
	public function beforeSave() {
		Service_Debug::setDebug();
		//Удаляем из сохраняемого массива поля с пустыми значениями
		$arrCheck = $this->tableFields;
		foreach ($arrCheck as $key => $value)
			if ($value=='') $arrCheck[$key] = null;
		$this->tableFields = $arrCheck;
	}
	
	public function afterCreate() {
		if($this->getField('ID_user')) {
			$objUser = new User_User($this->getField('ID_user'));
			if(!$objUser->getField('phone')) $objUser->updateField('phone', $this->getField('one_phone'));
		}
		
	}
	
	public function beforeCreate() {
		Service_Debug::setDebug();
		$objSession = User_Session::instance();
		if (!$objSession->isLoggedIn() && isset($this->tableFields['first_name'])) {
			// Получаем массив данных для регистрации пользователя
			$copiedFieldsArray = array ('first_name', 'last_name', 'pass', 'pass_confirmation', 'e_mail');
			foreach ($copiedFieldsArray as $copiedField) {
				// Получаем массив данных для регистрации пользователя, удаляя их при этом из набора данных заказа
				$userArray[$copiedField] = $this->tableFields[$copiedField];
				unset($this->tableFields[$copiedField]);
			}
			$objUser = new User_User($userArray);
			
			// Чтобы не требовал пользовательского соглашения
			unset($objUser->validatesAcceptanceOf['confirm']);
			$objUser->save();
			$objAuth = new User_Auth();
			$objAuth->signInSuccess($objUser->id);
		}
		if(SimpleAdministration::isAdmin()) $this->setField('client_agree', 1);
		if (isset($this->tableFields['kaptcha'])) unset($this->tableFields['kaptcha']);
		$this->setField('ID_user', $objSession->getUserId() ? $objSession->getUserId() : null);
	}
	
	/**
	 * функция получает водителя для текущего заказа
	 */
	public function getDriver() {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		$stmt = "SELECT  `driver`.`ID_driver`,`driver`.`first_name`,`driver`.`last_name` FROM `order` INNER JOIN `driver` USING(`ID_driver`) WHERE `order`.`ID_order` ='" . $this->id . "'";
		return $objDB->selectOne($stmt);
	}
	
	/**
	 * Функция получает переписку между админом и заказчиком по текущему заказу
	 */
	public function getComments() {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		$stmt = "SELECT `order_comment`.`ID_order_comment`,`order_comment`.`text`,`order_comment`.`attach_file_name`,`order`.`ID_user`,`user`.`first_name`,`user`.`last_name`,`user`.`is_admin`  FROM `order` INNER JOIN `order_comment` USING(`ID_order`) INNER JOIN `user` ON `user`.`ID_user`=`order_comment`.`ID_user` WHERE `order`.`ID_order` ='" . $this->id . "' ORDER BY `order_comment`.`created_at` ASC";
		return $objDB->select($stmt);
	}
	
	/**
	 * Функция подбирает подходящих водителей для текущего заказа
	 *
	 */
	public function getSuitableDrivers($weightCategory, $distance) {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		/*
		$stmt = "SELECT `driver`.`ID_driver`,`driver`.`first_name`,`driver`.`last_name`,`car`.`carrying_capacity`,`distance_wish`.`km_from`,`distance_wish`.`km_to`
				FROM `driver` 
				INNER JOIN `car` USING (`ID_driver`)  
				INNER JOIN `distance_wish` USING (`ID_driver`)  
				WHERE `distance_wish`.`km_from` <= " . $distance . " AND  `distance_wish`.`km_to` >=" . $distance . ($weightCategory ? " OR `car`.`carrying_capacity`='" . $weightCategory . "'" : '') . '';
		*/
		
		$stmt = "SELECT `driver`.`ID_driver`,`driver`.`first_name`,`driver`.`last_name`,`car`.`carrying_capacity`,`distance_wish`.`km_from`,`distance_wish`.`km_to`
		FROM `driver`
 		LEFT JOIN `car` ON `car`.`ID_driver`=`driver`.`ID_driver` " . ($weightCategory ? "AND `car`.`carrying_capacity`='" . $weightCategory . "'" : "") . "
		LEFT JOIN `distance_wish` ON `distance_wish`.`ID_driver`=`driver`.`ID_driver` AND `distance_wish`.`km_from` <= " . $distance . " AND  `distance_wish`.`km_to` >=" . $distance .
		" WHERE `distance_wish`.`km_from` IS NOT NULL  OR `car`.`carrying_capacity` IS NOT NULL";
		
		
		$driversArray = $objDB->select($stmt);

		if(empty($driversArray)) return null;
		else {
			$resultArray = array();
			foreach($driversArray as $driverArray) {
				if(!isset($resultArray[$driverArray['ID_driver']])) {
					$resultArray[$driverArray['ID_driver']]['first_name'] =  $driverArray['first_name'];
					$resultArray[$driverArray['ID_driver']]['last_name'] =  $driverArray['last_name'];
					if($weightCategory && $driverArray['carrying_capacity'] == $weightCategory) $resultArray[$driverArray['ID_driver']]['weight'] = true;
					elseif(!$weightCategory) $resultArray[$driverArray['ID_driver']]['weight'] = true;
					else $resultArray[$driverArray['ID_driver']]['weight'] = false;
					if($driverArray['km_from']) $resultArray[$driverArray['ID_driver']]['distance'] = true;
					else $resultArray[$driverArray['ID_driver']]['distance'] = false;
				}
			}
		}
		return $resultArray;
	}
	
	/**
	 * Функция Формирует массив отрезков маршрута текущего заказа
	 */
	public function getRouteArray() {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		$stmt = "SELECT `distance`.`ID_one_city`,`distance`.`ID_another_city`,`distance`.`distance`,`c1`.`city_name` AS `one_city_name`,`c1`.`latitude` AS `one_city_latitude`,`c1`.`longitude` AS `one_city_longitude`,`c1`.`no_on_map` AS `no_on_map_one_city`,
					 `c2`.`city_name` AS `another_city_name`, `c2`.`latitude` AS `another_city_latitude`,`c2`.`longitude` AS `another_city_longitude`,`c2`.`no_on_map` AS `no_on_map_another_city`
				FROM `route`
  				INNER JOIN `distance_route` USING(`ID_route`)
  				INNER JOIN `distance` USING (`ID_distance`)
  				INNER JOIN `city` `c1` ON `c1`.`ID_city` = `distance`.`ID_one_city`
  				INNER JOIN `city` `c2` ON `c2`.`ID_city` = `distance`.`ID_another_city`
				WHERE (`route`.`ID_one_city` = '" . $this->getField('city_from') . "' AND   `route`.`ID_another_city` = '" . $this->getField('city_to') . "') OR
				(`route`.`ID_one_city` = '" . $this->getField('city_to') . "' AND  `route`.`ID_another_city` = '" . $this->getField('city_from') . "')";
		$distancesArray = $objDB->select($stmt);
		
		$distancesResultArray = array();
		if(!empty($distancesArray)) {
			$currentPoint = $this->getField('city_from');
			$i = 1;
			$distancesInRoute = count($distancesArray);
			while(!empty($distancesArray)) {
				foreach($distancesArray as $key => $distanceArray) {
					if($distanceArray['ID_one_city'] == $currentPoint) {
						$distancesResultArray[] =  array('city_name' => $distanceArray['one_city_name'], 'distance_to_next' => $distanceArray['distance'], 'latitude' => $distanceArray['one_city_latitude'], 'longitude' => $distanceArray['one_city_longitude'], 'no_on_map' =>  $distanceArray['no_on_map_one_city'] == 1 ? 1 : 0 );
						$currentPoint = $distanceArray['ID_another_city'];
						if($i != $distancesInRoute) unset($distancesArray[$key]);
						else {
							$distancesResultArray[] = array('city_name' => $distanceArray['another_city_name'], 'distance_to_next' => 0, 'latitude' => $distanceArray['another_city_latitude'], 'longitude' => $distanceArray['another_city_longitude'],'no_on_map' =>  $distanceArray['no_on_map_another_city'] == 1 ? 1 : 0 );
							unset($distancesArray[$key]);
						}
						break;
					}
					elseif($distanceArray['ID_another_city'] == $currentPoint) {
						$distancesResultArray[] =array('city_name' => $distanceArray['another_city_name'], 'distance_to_next' => $distanceArray['distance'], 'latitude' => $distanceArray['another_city_latitude'], 'longitude' => $distanceArray['another_city_longitude'],'no_on_map' =>  $distanceArray['no_on_map_another_city'] == 1 ? 1 : 0 );
						$currentPoint = $distanceArray['ID_one_city'];
						if($i != $distancesInRoute) unset($distancesArray[$key]);
						else {
							$distancesResultArray[] =  array('city_name' => $distanceArray['one_city_name'], 'distance_to_next' => 0, 'latitude' => $distanceArray['one_city_latitude'], 'longitude' => $distanceArray['one_city_longitude'], 'no_on_map' =>  $distanceArray['no_on_map_one_city'] == 1 ? 1 : 0 );
							unset($distancesArray[$key]);
							}
						break;
					}
				}
				$i++;
			}
		}
		return $distancesResultArray;
	}
	
	/**
	 * Функция получает заказы(наши + финист) по маршруту(для страницы админа "Поиск маршрутов")
	 * 
	 * @param int $idOneCity - id города отправления
	 * @param int $idAnotherCity - id города назначения
	 * @param int $routeLength - длина маршрута(она вычисляется ранее, и, чтобы снова этого не делать - передаём)
	 */
	public static function getRouteOrders($idOneCity, $idAnotherCity, $routeLength) {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		$stmt = "SELECT `order`.`description`,`order`.`clients_price` price, DATE_FORMAT(`order`.`created_at`, '%e-%m-%Y') as date, `order`.`one_phone`
				FROM `order`
				WHERE (`order`.`city_from`='" . $idOneCity . "' AND `order`.`city_to`='" . $idAnotherCity . "') OR (`order`.`city_from`='" . $idAnotherCity . "' AND `order`.`city_to`='" . $idOneCity . "')
				LIMIT 20";
		$result = $objDB->select($stmt);
		
		if(count($result) < 20) {
			$stmt = "SELECT `finist`.`price_client` price,  DATE_FORMAT(`finist`.`date`, '%e-%m-%Y') as date, `finist`.`ID_finist` `is_fin`, `finist`.`price_client_hour` `price_hour`, `finist`.`place_start`,`finist`.`place_finish`
				FROM `finist`
				WHERE (`finist`.`ID_place_start`='" . $idOneCity . "' AND `finist`.`ID_place_finish`='" . $idAnotherCity . "') OR (`finist`.`ID_place_start`='" . $idAnotherCity . "' AND `finist`.`ID_place_finish`='" . $idOneCity . "') AND  `finist`.`price_client` != 0
				LIMIT " . (20 - count($result)) . "";
			$addResult = $objDB->select($stmt);
		}
		$result = array_merge($result, $addResult);
		$result = Service_ArrayConverter::sortArray($result, 'price');
		
		if(count($result) < 20) { // Получаем заказы похожи по километражу
			$stmt = "SELECT (`finist`.`route_distance` - " . $routeLength . ") distance_difference, `finist`.`route_distance` `distance`, `c1`.`city_name` `from`, `c2`.`city_name` `to`, DATE_FORMAT(`finist`.`date`, '%e-%m-%Y') as date, `finist`.`price_client` `price`,  `finist`.`price_client_hour` `price_hour`, `finist`.`place_start`,`finist`.`place_finish`
					FROM `finist`
					INNER JOIN city `c1` ON `finist`.`ID_place_start`=`c1`.`ID_city`
					INNER JOIN city `c2` ON `finist`.`ID_place_finish`=`c2`.`ID_city`
					WHERE `finist`.`route_distance` IS NOT NULL AND  `finist`.`price_client` != 0 AND ((`finist`.`ID_place_start` !='" . $idOneCity . "' AND `finist`.`ID_place_start` !='" . $idAnotherCity . "') OR (`finist`.`ID_place_finish` !='" . $idOneCity . "' AND `finist`.`ID_place_finish` !='" . $idAnotherCity . "'))
					GROUP BY `finist`.`ID_place_start`,`finist`.`ID_place_finish`
					ORDER BY ABS(distance_difference)
					LIMIT " . (20 - count($result)); 
			$addResult = $objDB->select($stmt);
			
			$result = array_merge($result, $addResult);
		}
		return $result;	
	}
	
	/**
	 * Функция получает статистику по заказам(для администратора)
	 */
	public static function getStat() {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		$stmt = "SELECT `distance`,COUNT(`distance`) `count`, ROUND(AVG(`price`/`distance`)) `midd_km_price`, ROUND(MIN(`price`/`distance`)) `min_km_price`, ROUND(MAX(`price`/`distance`)) `max_km_price`, MIN(`price`) `min_price`, MAX(`price`) `max_price`, ROUND(AVG(`price`)) `midd_price`
				FROM
				(SELECT `finist`.`route_distance` `distance`, `finist`.`price_client` `price`
				FROM `finist`
				WHERE `finist`.`route_distance` IS NOT NULL AND `finist`.`price_client` != 0
				UNION
				SELECT `order`.`clients_price` `price`, `route`.`distance`
				FROM `order`
				INNER JOIN `route` ON (`route`.`ID_one_city`=`order`.`city_from` AND `route`.`ID_another_city`=`order`.`city_to`) OR (`route`.`ID_one_city`=`order`.`city_to` AND `route`.`ID_another_city`=`order`.`city_from`)
				WHERE `order`.`clients_price` IS NOT NULL)temp
				GROUP BY temp.distance <= 100, temp.distance > 100 AND temp.distance <= 300, temp.distance > 300 AND temp.distance <= 500, temp.distance > 500 AND temp.distance <= 1000, temp.distance > 1000 AND temp.distance <= 2000, temp.distance > 2000  
				ORDER BY `distance`";
		$result = $objDB->select($stmt);
		return $result;
	}
	
	/**
	 * 
	 * @param int $idDriver - id  таблице driver текущего пользователя, есл ион водитель
	 */
	public static function getMoneyData($idDriver = null) {
		Service_Debug::setDebug();
		$objDB = Service_Database::instance();
		if($idDriver) {
			$stmt = "SELECT `order`.`ID_order`,`order`.`ID_user`, `order`.`loaded`, `order`.`unloaded`, DATE_FORMAT(`order`.`created_at`, '%e-%m-%Y') as date,`order`.`our_price` `price`,`c_from`.`city_name` AS `from`,`c_to`.`city_name` AS `to`
					FROM `order`
					INNER JOIN `city` `c_from` ON (`c_from`.`ID_city`=`order`.`city_from`) INNER JOIN `city` `c_to` ON (`c_to`.`ID_city`=`order`.`city_to`)
					WHERE `order`.`ID_driver`='" . $idDriver . "' AND `order`.`driver_pay` = 1 AND `order`.`canceled` = 0 AND `order`.`our_price` IS NOT NULL
					ORDER BY `order`.`created_at` DESC";
		}
		else {
			$stmt = "SELECT `order`.`ID_order`,`order`.`ID_user`, `order`.`loaded`, `order`.`unloaded`, `order`.`description`, DATE_FORMAT(`order`.`created_at`, '%e-%m-%Y') as date,`order`.`clients_price` `price`,`c_from`.`city_name` AS `from`,`c_to`.`city_name` AS `to`
					FROM `order`
					INNER JOIN `city` `c_from` ON (`c_from`.`ID_city`=`order`.`city_from`) INNER JOIN `city` `c_to` ON (`c_to`.`ID_city`=`order`.`city_to`)
					WHERE `order`.`ID_user`='" . User_User::getId() . "' AND `order`.`client_pay` = 1 AND `order`.`canceled` = 0 AND `order`.`clients_price` IS NOT NULL
					ORDER BY `order`.`created_at` DESC";
		}
		return $objDB->select($stmt);
	}
}
?>